# app.py
import streamlit as st
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
import numpy as np
import platform

# 设置中文字体
if platform.system() == 'Darwin':  # macOS
    plt.rcParams['font.sans-serif'] = ['Arial Unicode MS', 'Heiti TC', 'PingFang HK']
else:  # Windows
    plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False

# 设置 Seaborn 的字体
sns.set_theme(font='Arial Unicode MS')

# Streamlit 页面设置
st.set_page_config(page_title="学生消费数据可视化报表", layout="wide")

st.title("📊 学生消费数据可视化报表")
st.markdown("演示数据")

# 使用模拟数据
@st.cache_data(ttl=600)
def load_data(query):
    # 模拟数据
    if "monthly_trend" in query:
        df = pd.DataFrame({
            'year': np.repeat([2023, 2024], 12),
            'month': list(range(1, 13)) * 2,
            'total_amount': np.random.normal(10000, 2000, 24)
        })
        return df
    
    elif "department_consume_compare" in query:
        departments = ['计算机学院', '经济学院', '医学院', '文学院', '理学院']
        df = pd.DataFrame({
            'department': departments,
            'avg_consume': np.random.normal(800, 100, len(departments)),
            'total_consume': np.random.normal(1000000, 200000, len(departments))
        })
        return df
    
    elif "consume_type_distribution" in query:
        types = ['餐饮', '超市', '文具', '交通', '其他']
        df = pd.DataFrame({
            'consume_type': types,
            'total_amount': np.random.normal(50000, 10000, len(types))
        })
        return df
    
    elif "consume_level_distribution" in query:
        levels = ['低消费', '中等消费', '高消费', '超高消费']
        df = pd.DataFrame({
            'consume_level': levels,
            'student_count': np.random.randint(100, 1000, len(levels))
        })
        return df
    
    elif "time_based_behavior" in query:
        periods = ['早餐', '午餐', '晚餐', '夜宵']
        df = pd.DataFrame({
            'time_period': periods,
            'total_times': np.random.randint(1000, 5000, len(periods)),
            'avg_amount': np.random.normal(20, 5, len(periods))
        })
        return df
    
    elif "location_hotspot" in query:
        locations = ['第一食堂', '第二食堂', '图书馆', '学生超市', '校医院', '文具店']
        df = pd.DataFrame({
            'location': locations,
            'visit_count': np.random.randint(100, 1000, len(locations)),
            'total_amount': np.random.normal(50000, 10000, len(locations))
        })
        return df
    
    elif "student_consume_partitioned" in query:
        if "GROUP BY gender" in query:
            # 性别消费对比数据
            df = pd.DataFrame({
                'gender': ['男', '女'],
                'avg_amount': np.random.normal(100, 20, 2),
                'count': np.random.randint(1000, 2000, 2)
            })
            return df
        else:
            # 年龄分布数据
            df = pd.DataFrame({
                'age': np.random.normal(20, 2, 1000).astype(int)  # 生成1000个学生的年龄数据
            })
            return df
    
    return pd.DataFrame()  # 默认返回空数据框


# -------------------------------
# 1️⃣ 月度消费趋势
# -------------------------------
st.header("📈 月度消费趋势")

df_monthly = load_data("SELECT year, month, total_amount FROM monthly_trend ORDER BY year, month")
df_monthly['时间'] = df_monthly['year'].astype(str) + '-' + df_monthly['month'].astype(str).str.zfill(2)

fig1, ax1 = plt.subplots(figsize=(10,4))
sns.lineplot(data=df_monthly, x='时间', y='total_amount', marker='o', ax=ax1)
ax1.set_title("月度消费总金额趋势")
ax1.set_xlabel("时间")
ax1.set_ylabel("消费总金额")
ax1.tick_params(axis='x', rotation=45)
st.pyplot(fig1)


# -------------------------------
# 2️⃣ 学院消费对比
# -------------------------------
st.header("🏫 学院消费对比")
df_dept = load_data("SELECT department, avg_consume, total_consume FROM department_consume_compare")

fig2, ax2 = plt.subplots(figsize=(10,5))
sns.barplot(data=df_dept, y='department', x='total_consume', palette='Blues_d', ax=ax2)
ax2.set_title("各学院总消费对比")
ax2.set_xlabel("总消费金额")
ax2.set_ylabel("学院")
st.pyplot(fig2)


# -------------------------------
# 3️⃣ 消费类型分布（饼图）
# -------------------------------
st.header("🍱 消费类型金额占比")
df_type = load_data("SELECT consume_type, total_amount FROM consume_type_distribution")

fig3, ax3 = plt.subplots(figsize=(6,6))
ax3.pie(df_type['total_amount'], labels=df_type['consume_type'], autopct='%1.1f%%', startangle=140)
ax3.set_title("消费类型金额占比")
st.pyplot(fig3)


# -------------------------------
# 4️⃣ 消费等级分布
# -------------------------------
st.header("💰 消费等级分布")
df_level = load_data("SELECT consume_level, student_count FROM consume_level_distribution")

fig4, ax4 = plt.subplots(figsize=(6,4))
sns.barplot(data=df_level, x='consume_level', y='student_count', palette='Set2', ax=ax4)
ax4.set_title("学生消费等级分布")
ax4.set_xlabel("消费等级")
ax4.set_ylabel("学生人数")
st.pyplot(fig4)


# -------------------------------
# 5️⃣ 消费时间段画像
# -------------------------------
st.header("🕒 消费时间段画像")
df_time = load_data("""
    SELECT time_period, COUNT(*) AS total_times, ROUND(AVG(avg_amount),2) AS avg_amount
    FROM time_based_behavior
    GROUP BY time_period
""")

fig5, ax5 = plt.subplots(figsize=(6,4))
sns.barplot(data=df_time, x='time_period', y='avg_amount', palette='Oranges', ax=ax5)
ax5.set_title("不同时段平均消费额")
ax5.set_xlabel("消费时段")
ax5.set_ylabel("平均消费金额")
st.pyplot(fig5)


# -------------------------------
# 6️⃣ 热门消费地点
# -------------------------------
st.header("📍 热门消费地点")
df_location = load_data("SELECT location, visit_count, total_amount FROM location_hotspot LIMIT 10")

fig6, ax6 = plt.subplots(figsize=(10,5))
sns.barplot(data=df_location, y='location', x='visit_count', palette='coolwarm', ax=ax6)
ax6.set_title("最受欢迎的消费地点")
ax6.set_xlabel("访问次数")
ax6.set_ylabel("地点")
st.pyplot(fig6)

# 加载数据
df_monthly = load_data("SELECT year, month, total_amount FROM monthly_trend ORDER BY year, month")
df_monthly['时间'] = df_monthly['year'].astype(str) + '-' + df_monthly['month'].astype(str).str.zfill(2)

# 添加交互控件
years = sorted(df_monthly['year'].unique())
selected_years = st.multiselect("📅 选择年份查看消费趋势", years, default=years)

# 过滤数据
filtered_df = df_monthly[df_monthly['year'].isin(selected_years)]

# 绘图
fig, ax = plt.subplots(figsize=(10,5))
sns.lineplot(data=filtered_df, x='时间', y='total_amount', marker='o', ax=ax)
ax.set_title("📈 月度消费趋势图")
ax.set_xlabel("时间")
ax.set_ylabel("消费总金额")
ax.tick_params(axis='x', rotation=45)
st.pyplot(fig)


st.header("👫 性别消费对比")

df_gender = load_data("""
    SELECT gender, ROUND(AVG(amount), 2) AS avg_amount, COUNT(*) AS count
    FROM student_consume_partitioned
    GROUP BY gender
""")

fig, ax = plt.subplots(figsize=(6,4))
sns.barplot(data=df_gender, x='gender', y='avg_amount', palette='pastel', ax=ax)
ax.set_title("👫 性别平均消费金额对比")
st.pyplot(fig)


st.header("🎂 学生年龄分布")

df_age = load_data("SELECT age FROM student_consume_partitioned")

fig, ax = plt.subplots(figsize=(8,4))
sns.histplot(data=df_age, x='age', bins=10, kde=True, color='skyblue', ax=ax)
ax.set_title("🎂 年龄分布图")
st.pyplot(fig)


st.header("📤 数据导出")

st.markdown("点击按钮导出月度消费数据：")

csv = df_monthly.to_csv(index=False).encode('utf-8')
st.download_button(
    label="📥 下载 CSV 文件",
    data=csv,
    file_name='monthly_trend.csv',
    mime='text/csv'
)





# -------------------------------
# 🔎 数据表展示（可选）
# -------------------------------
st.header("📋 原始数据展示（部分）")
if st.checkbox("显示月度消费数据"):
    st.dataframe(df_monthly.head(20))

if st.checkbox("显示学院消费数据"):
    st.dataframe(df_dept.head(20))

st.markdown("---")
st.caption("© 2025 学生消费分析系统 | 演示数据")